<?php

/**
 * Decorator pattern for Zend_Db adapter
 * @author ksnyder
 */
class Quad_Db {
	
	protected static $_instances = array();
		
	protected $_adapter;
	protected $_transDepth = 0;
	protected $_loggerEnabled = false;
	
	public function __construct($adapter, $cfg) {
		if ($adapter == 'Quad:Mysql') {
			$this->_adapter = new Quad_Db_Adapter_Mysql($cfg);
		}
		else {
			if (!class_exists('Zend_Db', true)) {
				throw new Quad_Exception("Zend Framework's Zend_Db must be accessible to use db adapter `$adapter`.");
			}
			$this->_adapter = Zend_Db::factory($adapter, $cfg);
		}
	}
	
	public function enableLogging() {
		$this->_loggerEnabled = true;
	}
	
	public function disableLogging() {
		$this->_loggerEnabled = false;
	}
	
	public function getAdapter() {
		return $this->_adapter;
	}
	
	public static function get($name = 'default', $adapter = null, $cfg = null) {
		if (!isset(self::$_instances[$name])) {
			self::$_instances[$name] = new self($adapter, $cfg);
		}
		return self::$_instances[$name];
	}
	
	public function startTransaction() {
		$ok = true;
		if ($this->_transDepth == 0) {
			$ok = $this->query("START TRANSACTION");
//Quad_Debug::log('START TRANSACTION');		
		}
		$this->_transDepth++;
//Quad_Debug::log('startTransaction ' . $this->_transDepth);		
		return $ok;
	}
	
	public function commit() {
		$ok = true;
		if ($this->_transDepth == 1) {
			$ok = $this->query("COMMIT");
//Quad_Debug::log('COMMIT');	
		}
		$this->_reduceTransDepth();
//Quad_Debug::log('commit ' . $this->_transDepth);		
		return $ok;
	}
	
	public function rollback() {
		$ok = true;
		if ($this->_transDepth == 1) {
			$ok = $this->query("ROLLBACK");
//Quad_Debug::log('ROLLBACK');			
		}
		$this->_reduceTransDepth();
//Quad_Debug::log('rollback ' . $this->_transDepth);		
		return $ok;
	}
	
	public function commitOrRollback($doCommit) {
		if ($doCommit) {
			return $this->commit();
		}
		else {
			return $this->rollback();
		}
	}
	
	protected function _reduceTransDepth() {
		if ($this->_transDepth > 0) {
			$this->_transDepth--;
		}
	}
	
	public function isInTransaction() {
		return ($this->_transDepth > 0);
	}
	
	public function errorCode() {
		$code = $this->_adapter->getConnection()->errorCode();
		return $code;
	}
	
	public function errorMessage() {
		$info = $this->errorInfo();
		$msg = $info[2];
		return $msg;
	}	
	
	public function hasError() {
		$has = $this->errorCode() != '00000';
		return $has;
	}
	
	public function errorInfo() {
		$info = $this->_adapter->getConnection()->errorInfo();
		return $info;
	}
	
	public function query($sql, $bind = array()) {
		$this->bindVars($sql, $bind);
		return $this->_adapter->query($sql);
	}	
	
	public function select($sql, $bind = array(), $limit = -1, $offset = 0) {
		$this->bindVars($sql, $bind);
		if ($limit > 0 && $offset >= 0) {
			$sql = $this->_adapter->limit($sql, $limit, $offset);
		}		
		return $this->_adapter->fetchAll($sql);
	}
	
	public function selectFirst($sql, $bind = array()) {
		$this->bindVars($sql, $bind);
		return $this->_adapter->fetchRow($sql);
	}
	
	public function selectValue($sql, $bind = array()) {
		$this->bindVars($sql, $bind);
		return $this->_adapter->fetchOne($sql);
	}
	
	public function selectPairs($sql, $bind = array()) {
		$this->bindVars($sql, $bind);
		return $this->_adapter->fetchPairs($sql);
	}		
		
	public function insert($table, $values) {
		$this->logger("INSERT $table", $values);
		$affectedRows = $this->_adapter->insert($table, $values);
		if ($affectedRows > 0) {
			$id = $this->_adapter->lastInsertId();
			return ($id ? $id : true);
		}
		return false;
	}
	
	public function update($table, $values, $criteria = null) {
		$this->logger("UPDATE $table", $values, $criteria);
		if (is_array($criteria)) {
			$where = array();
			foreach ($criteria as $cond => $value) {
				if (is_int($cond) || strpos($cond, '?') >= 2) {
					$where[$cond] = $value;
				}
				else {
					$where["$cond = ?"] = $value;
				}
			}
		}
		else {
			$where = $criteria;			
		}		
		$result = $this->_adapter->update($table, $values, $where);
		return $result;
	}	
	
	public function bindVars(&$sql, $vals) {
		$this->logger($sql, $vals);
		if (is_array($vals)) {
			// TODO: use regex replace to avoid double escaping
			// e.g. bound vals of "id" and "id_no" will mess up id_no if id is replaced first
			foreach ($vals as $name => $val) {
				$replace = '';
				if ($val instanceof Quad_Db_Expr || $val instanceof Zend_Db_Expr) {
					$replace = (string) $val;
				}
				elseif ($val === null) {
					// TODO: make work in WHERE expressions with IS NULL
					$replace = 'NULL';
				}
				elseif (is_array($val)) {
					$in = array();
					foreach ($val as $item) {
						$in[] = $this->_adapter->quote($item);
					}
					$joined = join(',', $in);
					// replace `= :name` with `IN(...)`
					$sql = preg_replace("/=\s+:$name/", "IN($joined)", $sql); 
					// OR replace `:name` with `...`
					$sql = str_replace(":$name", $joined, $sql);
					continue;
				}
				elseif ($val === true) {
					$replace = '1';
				}
				elseif ($val === false) {
					$replace = '0';
				}
				else {
					$replace = $this->_adapter->quote($val);
				}
				$sql = str_replace(":$name", $replace, $sql);
			}
		}
		return $this;
	}
	
	public function __call($m,$a) {
		return call_user_func_array(array($this->_adapter, $m), $a);
	}
	
	public function __get($p) {
		return $this->_adapter->$p;
	}
	
	public function __set($p,$v) {
		$this->_adapter->$p = $v;
	}
	
	public function logger($sql, $data1 = null, $data2 = null) {
		if ($this->_loggerEnabled) {
			$normalized = self::normalizeSql($sql);
			error_log("$normalized\n" . json_encode(array($data1, $data2)));
		}
		return $this;
	}
	
	public static function normalizeSql($sql) {
		$unindented = preg_replace('/(^\s|\s$)/m','',trim($sql));
		return $unindented;
	}
	
	public static function hashSql($sql, $vals = null) {
		$unspaced = preg_replace('/\s+/',' ', $sql);
		return md5($unspaced);
	}
	
}

//<?php
//
//class Quad_Db extends Quad_Observable  {
//	  
//  protected static $_instances = array();
//  	
//	/**
//	 * Run a query and automatically add SQL_CALC_FOUND_ROWS and LIMIT, run SELECT_FOUND_ROWS and generate paging html
//	 *
//	 * @param string $sql
//	 * @param int $perPage   The number of results to allow per page (LIMIT)
//	 * @param int $page      The page number of results to retreive
//	 * @param string $labelPrefix  The prefix for the "Viewing Items 1-10 of 87" type label
//	 * @return array
//	 * <pre>
//	 * Example usage:
//	 *   list($results, $html) = dbQueryPage('SELECT * FROM problems', 10, 1)
//	 * </pre>
//	 */
//	public static function _selectPaged($sql, $values = array(), $perPage, $page, $labelPrefix = 'Viewing Items') {
//	  // set the limit
//	  $limit = (int) $perPage;
//	  // calculate the offset
//	  $offset = ($page - 1) * $perPage;
//	  // add SQL_CALC_FOUND_ROWS
//	  $newSql = preg_replace('/^select\s/i', 'SELECT SQL_CALC_FOUND_ROWS ', trim($sql), 1);
//	  // add LIMIT
//	  $newSql .= "\nLIMIT $offset, $limit";
//	  // run the query
//	  $results = self::select($newSql, $values);
//	  if (count($results)) {
//	    // get the number of rows that would have been returned if the query wasn't limited
//	    $totalRows = self::selectValue('SELECT FOUND_ROWS()');
//	    // avoid an orphan result
//	    if ($totalRows == ($perPage * $page) + 1) {
//	    	$perPage++;
//	    	$pager = self::selectPaged($sql, $values, 1, $page + 1, false);
//	    	$results[] = $pager->rs[0];
//	    }
//	    // calculate the number of result pages
//	    $numPages = ceil($totalRows / $perPage);
//	    // build the label for the html
//	    $max = $perPage * $page;
//	    if ($labelPrefix == false) {
//	    	$html = false;
//		  } else {
//		    $label = sprintf('%s %s&ndash;%s of %s', $labelPrefix, $offset + 1, ($max > $totalRows ? $totalRows : $max), $totalRows);
//		    // generate the html
//		    $html = Html::pager($page, $numPages, $label);
//		  }
//	  } else {
//	  	// no results
//	  	$results = array();
//	  	$html = '';
//	  	$totalRows = 0;
//	  	$numPages = 0;
//	  }
//    // return applicable values in an object
//    $pager = new stdClass();
//    $pager->rs = $results;	  
//    $pager->html = $html;	  
//    $pager->totalRows = $totalRows;	  
//    $pager->numPages = $numPages;
//    return $pager;	  
//	}
//	
//	/**
//	 * Look in the get, post and session string to find what page of results is requested
//	 *
//	 * @return int
//	 */
//	public static function _getResultPage($prefix = '') {
//	  // create a unique session variable
//	  $sessionVarName = $prefix . 'resultPage-' . $_SERVER['PHP_SELF'];
//	  $sessionPage = (int) $_SESSION[$sessionVarName];
//	  // find "page" in get/post which could be a number or next or prev
//	  $page = request('page', CLEAN_ASCII);
//	
//	  if ($page == '') {
//	    // nothing requested, so it's a new request, reset page count
//	    #$page = $sessionPage;
//	    
//	    $page = 1;
//	  } else {
//	    // request: check if it is prev, next, or an integer
//	    if (stripos($page, 'prev') !== false) {
//	      $page = $sessionPage - 1;
//	    } elseif (stripos($page, 'next') !== false) {
//	      $page = $sessionPage ? $sessionPage + 1 : 2;
//	    }
//	  }
//	  $page = (int) $page;
//	  if ($page <= 0) {
//	    // page still doesn't have a value or has a negative value so default to 1
//	    $page = 1;
//	  }
//	  // store the final result into session
//	  $_SESSION[$sessionVarName] = $page;
//	  return $page;
//	}	
//	
//
///**
// * Restrict an order by string to contain only allowed fields and ASC/DESC
// * <pre>
// * Examples:
// *   restrictOrderBy('color, size DESC; DROP inventory; --', array('color', 'size'), 'size');
// *   // 'color, size DESC'
// *   restrictOrderBy('i like coconuts', array('color', 'size'), 'size');
// *   // 'size' 
// *   restrictOrderBy('', array('color', 'size'), 'size');
// *   // 'size'
// * </pre>
// * 
// * @param string $commaList
// * @param array $allowedFields
// * @param string $default
// * @return string
// */
//function restrictOrderBy(&$commaList, $allowedFields, $default) {
//  $items = explode(',', $commaList);
//  $restricted = array();
//  foreach ($items as $item) {
//    list ($field, $direction) = explode(' ', trim($item));
//    if (in_array($field, $allowedFields)) {
//      restrictToList($direction, array('ASC', 'DESC', 'asc', 'desc'), '');
//      $restricted[] = trim($field . ' ' . $direction);
//    }
//  }
//  if (count($restricted)) {
//    return join(', ', $restricted);
//  } else {
//    return $default;
//  }
//}
